package pers.nickerg.util;


import cn.hutool.core.util.StrUtil;
import pers.nickerg.bean.ColumnBean;
import pers.nickerg.bean.TableBean;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;
import java.util.stream.Collectors;

public class OracleUtil {
    public static Connection getConnection(Properties properties) {
        try {
            return JdbcUtil.getConnection(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    public static void releaseDB(PreparedStatement ps) {
        JdbcUtil.releaseDB(ps);
    }

    public static void releaseDB(ResultSet resultSet, Statement statement, Connection connection) {
        JdbcUtil.releaseDB(resultSet, statement, connection);
    }

    /**
     * 获取当前数据库下的所有表名称 和表注释
     */
    public static List<TableBean> getAllTableName() throws Exception {
        List<TableBean> tableList = new ArrayList<TableBean>();

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        TableBean tableBean = null;

        String sql = "select a.TABLE_NAME,b.COMMENTS from user_tables a,user_tab_comments b WHERE a.TABLE_NAME=b.TABLE_NAME";

        try {
            Properties properties = JdbcUtil.getProperties("oracle.properties");
            if (StrUtil.isNotBlank(properties.getProperty("jdbc.table"))) {
                String tableName = properties.getProperty("jdbc.table");
                String collect = Arrays.stream(tableName.split(",")).filter(StrUtil::isNotEmpty).map(String::toUpperCase).map(x -> "'" + x + "'").collect(Collectors.joining(",", " and a.table_name in (", ")"));
                sql += collect;
            }
            sql += " order by TABLE_NAME  ";
            conn = OracleUtil.getConnection(properties);
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                tableBean = new TableBean(rs.getString(1), rs.getString(2));
                tableList.add(tableBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            OracleUtil.releaseDB(rs, stmt, conn);
        }

        return tableList;
    }

    /**
     * 获取表列信息
     */
    public static List<ColumnBean> getColList(String tableName) {
        ArrayList<ColumnBean> colList = new ArrayList<ColumnBean>();
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
        ColumnBean columnBean = null;

        //String sql="select t1.COLUMN_NAME,t1.DATA_TYPE ,t1.DATA_LENGTH,t1.DATA_PRECISION,t1.DATA_SCALE,t1.NULLABLE,t2.comments,t5.column_name primaryKey"
        //+ " from all_tab_columns t1 left join user_col_comments t2 on t1.TABLE_NAME=t2.table_name and t1.COLUMN_NAME=t2.COLUMN_NAME "
        //+ " left join (select t3.table_name,t3.column_name from user_cons_columns t3, user_constraints t4 "
        //+ " where t3.constraint_name = t4.constraint_name and t4.constraint_type = 'P') t5 on t1.TABLE_NAME=t5.table_name and t1.COLUMN_NAME=t5.column_name"
        String sql = "select\n" + "    ut.COLUMN_NAME,--字段名称\n" + "    ut.DATA_TYPE,--字典类型\n" + "    ut.DATA_LENGTH,--字典长度\n" + "    ut.DATA_PRECISION,\n" + "    ut.DATA_SCALE,\n" + "    ut.NULLABLE,--是否为空\n" + "    uc.comments,--字段注释\n" + "    t5.column_name as primarykey\n" + "from user_tab_columns  ut\n" + "         inner JOIN user_col_comments uc\n" + "                    on ut.TABLE_NAME  = uc.table_name and ut.COLUMN_NAME = uc.column_name\n" + "         left join (select t3.table_name,t3.column_name from user_cons_columns t3, user_constraints t4  where t3.constraint_name = t4.constraint_name and t4.constraint_type = 'P') t5 on ut.COLUMN_NAME=t5.column_name and ut.TABLE_NAME=t5.table_name\n" + "where ut.Table_Name= '" + tableName + "' order by ut.column_id";


        try {
            Properties properties = JdbcUtil.getProperties("oracle.properties");
            conn = OracleUtil.getConnection(properties);
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            // System.out.println(sql);
            rs = stmt.executeQuery(sql);
            int dataLength = 0;
            int dataPrecision = 0;
            int dataScale = 0;
            while (rs.next()) {
                try {
                    dataLength = Integer.parseInt(rs.getString(3));
                } catch (NumberFormatException e) {
                    dataLength = 0;
                }
                try {
                    dataPrecision = Integer.parseInt(rs.getString(4));
                } catch (NumberFormatException e) {
                    dataPrecision = 0;
                }
                try {
                    dataScale = Integer.parseInt(rs.getString(5));
                } catch (NumberFormatException e) {
                    dataScale = 0;
                }
                columnBean = new ColumnBean(rs.getString(1), rs.getString(2), dataLength, dataPrecision, dataScale, rs.getString(6), rs.getString(7), rs.getString(8));
                colList.add(columnBean);
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            OracleUtil.releaseDB(rs, stmt, conn);
        }

        return colList;
    }

    /**
     * 获取oracle表数据
     */
    public static ResultSet getDataFromTable(String tableName) throws IOException {
        Properties properties = JdbcUtil.getProperties("oracle.properties");
        Connection oConn = OracleUtil.getConnection(properties);
        PreparedStatement pstm = null;

        try {
            String sql = "select * from " + tableName;
            pstm = oConn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            return pstm.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static ResultSet getDataFromTable(String tableName, Properties properties) {
        Connection oConn = OracleUtil.getConnection(properties);
        PreparedStatement pstm = null;
        try {
            String sql = "select * from " + tableName;
            pstm = oConn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            return pstm.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}